/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.handlers;

import database.Database;
import java.sql.SQLException;
import model.StorageList;

/**
 *
 * @author luoyu
 */
public abstract class StorageListHandler {
    /**
     *  CREATE OR REPLACE VIEW getProductNumbers AS
        (
                SELECT 
                        pf.name AS productFamilyName,
                        count(*) AS numProducts,
                        sum(purchasing_price) AS sumPurchase,
                        sum(sales_price) AS sumSalesPrice
                FROM 
                        bs_product AS p
                                INNER JOIN bs_product_family AS pf ON pf.id = p.fk_product_family
                GROUP BY
                        productFamilyName
        );
     * @return 
     */
    public static StorageList[] getStorageList() throws ClassNotFoundException, SQLException
    {
        StorageList[] storageList = null;
        
        Database database = new Database();
        String[][] results = database.getFromDB("getProductNumbers", "", "", "", "productFamilyID", "numProducts", "sumPurchase", "sumSalesPrice");
        database.closeDB();
        
        storageList = new StorageList[results.length];

        for (int i = 0; i < results.length; i++) {
            storageList[i] = new StorageList(ProductFamilyHandler.getProductFamilyByID(Integer.parseInt(results[i][0])), Integer.parseInt(results[i][1]),
                    Double.parseDouble(results[i][2]), Double.parseDouble(results[i][3]));
            
        }
        return storageList;
    }
    
    /**
     * 
        CREATE OR REPLACE VIEW getProductNumbers AS
        (
                SELECT 
                        pf.name AS productFamilyName,
                        count(*) AS sumProducts,
                        sum(purchasing_price) AS sumPurchase,
                        sum(sales_price) AS sumSalesPrice,
                        sold
                FROM 
                        bs_product AS p
                                INNER JOIN bs_product_family AS pf ON pf.id = p.fk_product_family
                GROUP BY
                        productFamilyName
        );

                select 
                        sum(numProducts) AS numProducts,
                        sum(sumPurchase) AS sumPurchase, 
                        sum(sumSalesprice) AS sumSalesprice
                from getProductNumbers
                where sold = 0
             * 
             */
    public static StorageList getStorageListSum() throws ClassNotFoundException, SQLException
    {
        StorageList storageList = null;
        
        Database database = new Database();
        String[][] results = database.getFromDB("getSumProductNumbers", "", "", "", "sumProducts", "sumPurchase", "sumSales");
        database.closeDB();
        storageList = new StorageList(Integer.parseInt(results[0][0]), Double.parseDouble(results[0][1]), Double.parseDouble(results[0][2]));
        
        return storageList;
    }
}
